package converter

import (
	"strings"
	"testing"

	"kwdb-import/pkg/parser"
)

// TestMySQLToKWDBConverter 测试MySQL到KaiwuDB的转换器
func TestMySQLToKWDBConverter(t *testing.T) {
	converter := NewMySQLToKWDBConverter()

	tests := []struct {
		name        string
		input       string
		stmtType    parser.StatementType
		expected    string
		shouldError bool
	}{
		// 数据库操作
		{
			name:     "CREATE DATABASE",
			input:    "CREATE DATABASE test_db;",
			stmtType: parser.CreateDatabase,
			expected: "CREATE DATABASE IF NOT EXISTS test_db;",
		},
		{
			name:     "USE DATABASE",
			input:    "USE test_db;",
			stmtType: parser.UseDatabase,
			expected: "\\c test_db;", // KaiwuDB使用\c切换数据库
		},

		// 表操作 - 数据类型转换
		{
			name:     "CREATE TABLE with MySQL types",
			input:    "CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), age TINYINT, created DATETIME);",
			stmtType: parser.CreateTable,
			expected: "CREATE TABLE IF NOT EXISTS users (id SERIAL PRIMARY KEY, name VARCHAR(50), age SMALLINT, created TIMESTAMP);",
		},
		{
			name:     "CREATE TABLE with ENUM",
			input:    "CREATE TABLE users (id INT, status ENUM('active', 'inactive', 'pending'));",
			stmtType: parser.CreateTable,
			expected: "CREATE TABLE IF NOT EXISTS users (id INTEGER, status VARCHAR(255) CHECK (status IN ('active', 'inactive', 'pending')));",
		},
		{
			name:     "CREATE TABLE with MySQL options",
			input:    "CREATE TABLE users (id INT) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;",
			stmtType: parser.CreateTable,
			expected: "CREATE TABLE IF NOT EXISTS users (id INTEGER);", // 移除MySQL特定选项并统一类型
		},

		// 索引操作
		{
			name:     "CREATE INDEX",
			input:    "CREATE INDEX idx_name ON users(name);",
			stmtType: parser.CreateIndex,
			expected: "CREATE INDEX IF NOT EXISTS idx_name ON users(name);",
		},
		{
			name:     "CREATE UNIQUE INDEX",
			input:    "CREATE UNIQUE INDEX idx_email ON users(email);",
			stmtType: parser.CreateIndex,
			expected: "CREATE UNIQUE INDEX IF NOT EXISTS idx_email ON users(email);",
		},

		// 插入操作
		{
			name:     "INSERT statement",
			input:    "INSERT INTO users (name, email) VALUES ('张三', 'zhangsan@example.com');",
			stmtType: parser.Insert,
			expected: "INSERT INTO users (name, email) VALUES ('张三', 'zhangsan@example.com');",
		},

		// 视图操作
		{
			name:     "CREATE VIEW",
			input:    "CREATE VIEW active_users AS SELECT * FROM users WHERE status = 'active';",
			stmtType: parser.CreateView,
			expected: "CREATE VIEW active_users AS SELECT * FROM users WHERE status = 'active';",
		},

		// 其他语句
		{
			name:     "Other SQL statement",
			input:    "UPDATE users SET name = '李四' WHERE id = 1;",
			stmtType: parser.Other,
			expected: "UPDATE users SET name = '李四' WHERE id = 1;",
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			result, err := converter.Convert(parser.SQLStatement{Type: tt.stmtType, Content: tt.input}, nil)

			if tt.shouldError {
				if err == nil {
					t.Errorf("Expected error for input %q, but got none", tt.input)
				}
				return
			}

			if err != nil {
				t.Errorf("Unexpected error for input %q: %v", tt.input, err)
				return
			}

			if result != tt.expected {
				t.Errorf("Convert(%q, %v) = %q, want %q", tt.input, tt.stmtType, result, tt.expected)
			}
		})
	}
}

// TestDataTypeConversion 测试数据类型转换
func TestDataTypeConversion(t *testing.T) {
	converter := NewMySQLToKWDBConverter()

	tests := []struct {
		mysqlType string
		expected  string
	}{
		// 整数类型
		{"TINYINT", "SMALLINT"},
		{"SMALLINT", "SMALLINT"},
		{"MEDIUMINT", "INTEGER"},
		{"INT", "INTEGER"},
		{"INTEGER", "INTEGER"},
		{"BIGINT", "BIGINT"},

		// 浮点类型
		{"FLOAT", "REAL"},
		{"DOUBLE", "DOUBLE PRECISION"},
		{"DECIMAL", "DECIMAL"},

		// 字符串类型
		{"CHAR", "CHAR"},
		{"VARCHAR", "VARCHAR"},
		{"TEXT", "TEXT"},
		{"LONGTEXT", "TEXT"},
		{"MEDIUMTEXT", "TEXT"},
		{"TINYTEXT", "TEXT"},

		// 日期时间类型
		{"DATE", "DATE"},
		{"TIME", "TIME"},
		{"DATETIME", "TIMESTAMP"},
		{"TIMESTAMP", "TIMESTAMP"},
		{"YEAR", "SMALLINT"},

		// 二进制类型
		{"BINARY", "BYTEA"},
		{"VARBINARY", "BYTEA"},
		{"BLOB", "BYTEA"},
		{"LONGBLOB", "BYTEA"},
	}

	for _, tt := range tests {
		t.Run(tt.mysqlType, func(t *testing.T) {
			result := converter.convertDataTypes(tt.mysqlType, nil)
			if result != tt.expected {
				t.Errorf("convertDataType(%q) = %q, want %q", tt.mysqlType, result, tt.expected)
			}
		})
	}
}

// TestAutoIncrementConversion 测试AUTO_INCREMENT转换
func TestAutoIncrementConversion(t *testing.T) {
	converter := NewMySQLToKWDBConverter()

	tests := []struct {
		name     string
		input    string
		expected string
	}{
		{
			name:     "INT AUTO_INCREMENT",
			input:    "id INT AUTO_INCREMENT PRIMARY KEY",
			expected: "id SERIAL PRIMARY KEY",
		},
		{
			name:     "BIGINT AUTO_INCREMENT",
			input:    "id BIGINT AUTO_INCREMENT",
			expected: "id BIGSERIAL",
		},
		{
			name:     "No AUTO_INCREMENT",
			input:    "id INT PRIMARY KEY",
			expected: "id INTEGER PRIMARY KEY",
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			result := converter.convertAutoIncrement(tt.input)
			if result != tt.expected {
				t.Errorf("convertAutoIncrement(%q) = %q, want %q", tt.input, result, tt.expected)
			}
		})
	}
}

// TestEnumConversion 测试ENUM类型转换
func TestEnumConversion(t *testing.T) {
	converter := NewMySQLToKWDBConverter()

	tests := []struct {
		name           string
		inputDDL       string
		expectedSubstr string
	}{
		{
			name:           "Simple ENUM",
			inputDDL:       "CREATE TABLE t (status ENUM('active', 'inactive'));",
			expectedSubstr: "status VARCHAR(255) CHECK (status IN ('active', 'inactive'))",
		},
		{
			name:           "ENUM with more values",
			inputDDL:       "CREATE TABLE t (priority ENUM('low', 'medium', 'high', 'urgent'));",
			expectedSubstr: "priority VARCHAR(255) CHECK (priority IN ('low', 'medium', 'high', 'urgent'))",
		},
		{
			name:           "No ENUM",
			inputDDL:       "CREATE TABLE t (name VARCHAR(50));",
			expectedSubstr: "name VARCHAR(50)",
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			result, err := converter.Convert(parser.SQLStatement{Type: parser.CreateTable, Content: tt.inputDDL}, nil)
			if err != nil {
				t.Fatalf("Unexpected error: %v", err)
			}
			if !strings.Contains(result, tt.expectedSubstr) {
				t.Errorf("Expected converted DDL to contain %q, got %q", tt.expectedSubstr, result)
			}
		})
	}
}

// TestZeroLengthStringNormalization 测试将非法的零长度字符串类型规范化
func TestZeroLengthStringNormalization(t *testing.T) {
	converter := NewMySQLToKWDBConverter()

	cases := []struct {
		name     string
		inputDDL string
		wantSub  string
	}{
		{
			name:     "Normalize VARCHAR(0)",
			inputDDL: "CREATE TABLE qwe (test_name TEXT, test_code varchar(0) DEFAULT NULL);",
			wantSub:  "test_code VARCHAR(1) DEFAULT NULL",
		},
		{
			name:     "Normalize CHAR(0)",
			inputDDL: "CREATE TABLE t (c CHAR(0));",
			wantSub:  "c CHAR(1)",
		},
	}

	for _, tc := range cases {
		t.Run(tc.name, func(t *testing.T) {
			got, err := converter.Convert(parser.SQLStatement{Type: parser.CreateTable, Content: tc.inputDDL}, nil)
			if err != nil {
				t.Fatalf("unexpected error: %v", err)
			}
			if !strings.Contains(got, tc.wantSub) {
				t.Errorf("expected converted DDL to contain %q, got %q", tc.wantSub, got)
			}
		})
	}
}

// TestByteaLengthRemoval 测试移除 BYTEA 的长度参数
func TestByteaLengthRemoval(t *testing.T) {
	converter := NewMySQLToKWDBConverter()

	cases := []struct {
		name     string
		inputDDL string
		wantSub  string
		avoidSub string
	}{
		{
			name:     "VARBINARY(255) -> BYTEA",
			inputDDL: "CREATE TABLE t (id BIGINT, test_bin VARBINARY(255) DEFAULT NULL);",
			wantSub:  "test_bin BYTEA DEFAULT NULL",
			avoidSub: "BYTEA(255)",
		},
		{
			name:     "BINARY(32) -> BYTEA",
			inputDDL: "CREATE TABLE t2 (b BINARY(32));",
			wantSub:  "b BYTEA",
			avoidSub: "BYTEA(32)",
		},
		{
			name:     "LONGBLOB -> BYTEA",
			inputDDL: "CREATE TABLE t3 (data LONGBLOB);",
			wantSub:  "data BYTEA",
			avoidSub: "BYTEA(",
		},
	}

	for _, tc := range cases {
		t.Run(tc.name, func(t *testing.T) {
			got, err := converter.Convert(parser.SQLStatement{Type: parser.CreateTable, Content: tc.inputDDL}, nil)
			if err != nil {
				t.Fatalf("unexpected error: %v", err)
			}
			if !strings.Contains(got, tc.wantSub) {
				t.Errorf("expected converted DDL to contain %q, got %q", tc.wantSub, got)
			}
			if strings.Contains(got, tc.avoidSub) {
				t.Errorf("expected converted DDL to NOT contain %q, got %q", tc.avoidSub, got)
			}
		})
	}
}

// TestMySQLOptionsRemoval 测试MySQL选项移除
func TestMySQLOptionsRemoval(t *testing.T) {
	converter := NewMySQLToKWDBConverter()

	tests := []struct {
		name     string
		input    string
		expected string
	}{
		{
			name:     "ENGINE option",
			input:    "CREATE TABLE users (id INT) ENGINE=InnoDB;",
			expected: "CREATE TABLE users (id INT);",
		},
		{
			name:     "CHARSET option",
			input:    "CREATE TABLE users (id INT) DEFAULT CHARSET=utf8mb4;",
			expected: "CREATE TABLE users (id INT);",
		},
		{
			name:     "Multiple options",
			input:    "CREATE TABLE users (id INT) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;",
			expected: "CREATE TABLE users (id INT);",
		},
		{
			name:     "AUTO_INCREMENT option",
			input:    "CREATE TABLE users (id INT) AUTO_INCREMENT=1000;",
			expected: "CREATE TABLE users (id INT);",
		},
		{
			name:     "No options",
			input:    "CREATE TABLE users (id INT, name VARCHAR(50));",
			expected: "CREATE TABLE users (id INT, name VARCHAR(50));",
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			result := converter.removeMySQLOptions(tt.input)
			if result != tt.expected {
				t.Errorf("removeMySQLOptions(%q) = %q, want %q", tt.input, result, tt.expected)
			}
		})
	}
}

// TestConversionWarnings 测试转换警告
func TestConversionWarnings(t *testing.T) {
	converter := NewMySQLToKWDBConverter()

	// 执行一些可能产生警告的转换
	warningCases := []string{
		"CREATE TABLE users (id INT) ENGINE=InnoDB;",
		"CREATE TABLE posts (status ENUM('draft', 'published'));",
		"CREATE TABLE logs (created DATETIME);",
		"CREATE TABLE files (data LONGBLOB);",
	}

	var warnings []string
	for _, sql := range warningCases {
		stmt := parser.SQLStatement{Type: parser.CreateTable, Content: sql}
		_, err := converter.Convert(stmt, nil)
		if err != nil {
			t.Errorf("Unexpected error for %q: %v", sql, err)
		}
		ws := converter.GetConversionWarnings(stmt)
		warnings = append(warnings, ws...)
	}

	// 应该有一些警告
	if len(warnings) == 0 {
		t.Error("Expected some conversion warnings, but got none")
	}

	// 验证警告内容
	for _, warning := range warnings {
		if warning == "" {
			t.Error("Warning message should not be empty")
		}
		if !strings.Contains(warning, "转换") && !strings.Contains(warning, "警告") {
			t.Errorf("Warning message should contain conversion info: %q", warning)
		}
	}
}

// TestComplexTableConversion 测试复杂表结构转换
func TestComplexTableConversion(t *testing.T) {
	converter := NewMySQLToKWDBConverter()

	complexSQL := `CREATE TABLE users (
		id BIGINT AUTO_INCREMENT PRIMARY KEY,
		username VARCHAR(50) NOT NULL UNIQUE,
		email VARCHAR(100) NOT NULL,
		password_hash CHAR(64) NOT NULL,
		status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
		age TINYINT UNSIGNED,
		balance DECIMAL(10,2) DEFAULT 0.00,
		profile_data JSON,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
		INDEX idx_email (email),
		INDEX idx_status (status)
	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;`

	result, err := converter.Convert(parser.SQLStatement{Type: parser.CreateTable, Content: complexSQL}, nil)
	if err != nil {
		t.Fatalf("Unexpected error: %v", err)
	}

	// 验证转换结果包含预期的KaiwuDB语法
	expectedElements := []string{
		"BIGSERIAL PRIMARY KEY", // AUTO_INCREMENT转换
		"VARCHAR(50)",           // 保持VARCHAR
		"CHAR(64)",              // 保持CHAR
		"SMALLINT",              // TINYINT转换
		"DECIMAL(10,2)",         // 保持DECIMAL
		"TIMESTAMP",             // DATETIME转换
		"CHECK",                 // ENUM转换为CHECK约束
	}

	for _, element := range expectedElements {
		if !strings.Contains(result, element) {
			t.Errorf("Expected result to contain %q, but it doesn't. Result: %s", element, result)
		}
	}

	// 验证MySQL特定选项被移除
	mysqlOptions := []string{
		"ENGINE=InnoDB",
		"DEFAULT CHARSET=utf8mb4",
		"COLLATE=utf8mb4_unicode_ci",
		"ON UPDATE CURRENT_TIMESTAMP",
	}

	for _, option := range mysqlOptions {
		if strings.Contains(result, option) {
			t.Errorf("Result should not contain MySQL option %q, but it does. Result: %s", option, result)
		}
	}
}

// TestSkipUnsupportedRoutines 测试不支持的过程/函数/触发器被注释掉
func TestSkipUnsupportedRoutines(t *testing.T) {
	c := NewMySQLToKWDBConverter()
	proc := parser.SQLStatement{Type: parser.CreateProcedure, Content: "CREATE DEFINER=root@% PROCEDURE p() BEGIN SELECT 1; END"}
	fn := parser.SQLStatement{Type: parser.CreateFunction, Content: "CREATE DEFINER=root@% FUNCTION f() RETURNS INT BEGIN RETURN 1; END"}
	trg := parser.SQLStatement{Type: parser.CreateTrigger, Content: "CREATE DEFINER=root@% TRIGGER trg BEFORE INSERT ON t FOR EACH ROW BEGIN SET @x=1; END"}

	out1, err1 := c.Convert(proc, nil)
	out2, err2 := c.Convert(fn, nil)
	out3, err3 := c.Convert(trg, nil)
	if err1 != nil || err2 != nil || err3 != nil {
		t.Fatalf("unexpected error converting routines: %v %v %v", err1, err2, err3)
	}
	if out1 != "" {
		t.Errorf("procedure should be ignored (empty), got: %q", out1)
	}
	if out2 != "" {
		t.Errorf("function should be ignored (empty), got: %q", out2)
	}
	if out3 != "" {
		t.Errorf("trigger should be ignored (empty), got: %q", out3)
	}
}

// TestCreateViewOptionRemoval 测试移除MySQL视图选项
func TestCreateViewOptionRemoval(t *testing.T) {
	c := NewMySQLToKWDBConverter()
	input := "CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW v AS SELECT 1 WITH CHECK OPTION;"
	out, err := c.Convert(parser.SQLStatement{Type: parser.CreateView, Content: input}, nil)
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	if strings.Contains(strings.ToUpper(out), "ALGORITHM=") || strings.Contains(strings.ToUpper(out), "DEFINER=") || strings.Contains(strings.ToUpper(out), "SQL SECURITY") {
		t.Errorf("Output should not contain MySQL view options, got: %s", out)
	}
	if strings.Contains(strings.ToUpper(out), "OR REPLACE") {
		t.Errorf("Output should not contain OR REPLACE for target DB, got: %s", out)
	}
	if strings.Contains(strings.ToUpper(out), "WITH CHECK OPTION") {
		t.Errorf("Output should not contain WITH CHECK OPTION, got: %s", out)
	}
	upper := strings.ToUpper(out)
	if !strings.Contains(upper, "CREATE VIEW") || !strings.Contains(upper, "AS SELECT 1") {
		t.Errorf("Output should contain CREATE VIEW ... AS SELECT 1; got: %s", out)
	}
}

// BenchmarkConvert 转换性能测试
func BenchmarkConvert(b *testing.B) {
	converter := NewMySQLToKWDBConverter()
	sql := "CREATE TABLE users (id BIGINT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), status ENUM('active', 'inactive'), created DATETIME);"

	b.ResetTimer()
	for i := 0; i < b.N; i++ {
		_, err := converter.Convert(parser.SQLStatement{Type: parser.CreateTable, Content: sql}, nil)
		if err != nil {
			b.Fatalf("Convert failed: %v", err)
		}
	}
}

// BenchmarkDataTypeConversion 数据类型转换性能测试
func BenchmarkDataTypeConversion(b *testing.B) {
	converter := NewMySQLToKWDBConverter()
	mysqlTypes := []string{"TINYINT", "MEDIUMINT", "DATETIME", "LONGTEXT", "ENUM('a','b','c')"}

	b.ResetTimer()
	for i := 0; i < b.N; i++ {
		for _, mysqlType := range mysqlTypes {
			converter.convertDataTypes(mysqlType, nil)
		}
	}
}

// TestEdgeCases 测试边界情况
func TestEdgeCases(t *testing.T) {
	converter := NewMySQLToKWDBConverter()

	tests := []struct {
		name        string
		input       string
		stmtType    parser.StatementType
		shouldError bool
	}{
		{
			name:        "Empty string",
			input:       "",
			stmtType:    parser.Unknown,
			shouldError: false,
		},
		{
			name:        "Only whitespace",
			input:       "   \t\n  ",
			stmtType:    parser.Unknown,
			shouldError: false,
		},
		{
			name:        "Comment only",
			input:       "-- This is a comment",
			stmtType:    parser.Unknown,
			shouldError: false,
		},
		{
			name:        "Very long SQL",
			input:       strings.Repeat("CREATE TABLE test (id INT); ", 1000),
			stmtType:    parser.CreateTable,
			shouldError: false,
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			_, err := converter.Convert(parser.SQLStatement{Type: tt.stmtType, Content: tt.input}, nil)

			if tt.shouldError && err == nil {
				t.Error("Expected error but got none")
			} else if !tt.shouldError && err != nil {
				t.Errorf("Unexpected error: %v", err)
			}
		})
	}
}

// TestCommentConversion 测试将MySQL的列/表注释转换为PostgreSQL COMMENT ON语法
func TestCommentConversion(t *testing.T) {
	converter := NewMySQLToKWDBConverter()

	input := `CREATE TABLE test (
  id INT COMMENT '主键',
  name VARCHAR(20) COMMENT '名称'
) COMMENT='表注释';`

	out, err := converter.Convert(parser.SQLStatement{Type: parser.CreateTable, Content: input}, nil)
	if err != nil {
		t.Fatalf("unexpected error: %v", err)
	}

	// 不应保留内联列注释
	if strings.Contains(strings.ToUpper(out), " COMMENT '") {
		t.Errorf("inline COMMENT should be removed from CREATE TABLE, got: %s", out)
	}

	// 应追加 COMMENT ON 语句
	if !strings.Contains(out, "COMMENT ON TABLE test IS '表注释';") {
		t.Errorf("missing table comment conversion: %s", out)
	}
	if !strings.Contains(out, "COMMENT ON COLUMN test.id IS '主键';") {
		t.Errorf("missing column comment conversion for id: %s", out)
	}
	if !strings.Contains(out, "COMMENT ON COLUMN test.name IS '名称';") {
		t.Errorf("missing column comment conversion for name: %s", out)
	}
}
